package com.deng.mapper;

import com.deng.model.Comments;
import com.deng.model.Paper;
import com.deng.model.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.List;

/**
 * @Author 邓小熙
 * @Date 2022/5/9 21:26
 * @Version 1.0
 */
@Mapper
@Component
public interface PaperMapper {
    /**
     * 根据文章ID查询文章
     */
    @Select("SELECT * FROM social_blog.paper p WHERE p.id = #{id}")
    Paper findPaperById(int id);

    /**
     * 通过文章ID查询发布用户
     *
     * @param id
     * @return
     */
    @Select("SELECT social_blog.users.* FROM social_blog.users,social_blog.paper WHERE social_blog.paper.author = social_blog.users.id AND social_blog.paper.id = #{id}")
    User findUserByPaperId(int id);

    /**
     * 通过专题ID查询专题
     *
     * @param id
     * @return
     */
    @Select("SELECT social_blog.`column`.title FROM social_blog.column_paper,social_blog.paper,social_blog.`column` WHERE social_blog.`column`.id= social_blog.column_paper.Column_id AND social_blog.column_paper.paper_id = social_blog.paper.id AND social_blog.paper.id = #{id}")
    String findColumnsById(int id);

    /**
     * 通过ID查询评论
     *
     * @param id
     * @return
     */
    @Select("SELECT c.* " + "FROM social_blog.paper p, social_blog.comments c " + "WHERE p.id = c.comments_paper AND p.id =#{id}")
    List<Comments> findCommentsById(int id);

    /**
     * 首页按文章发布时间最新排序
     *
     * @return
     */
    @Select("SELECT * FROM social_blog.paper p WHERE is_pass=\"审核通过\" ORDER BY p.create_time DESC LIMIT 0,5")
    List<Paper> findAllPapersByTime();

    /**
     * 通过文章标题来查找发布用户
     *
     * @param id
     * @return
     */
    @Select("SELECT users.* FROM users,paper WHERE paper.author = users.id AND paper.id = #{id}")
    User findUserByPaperTitle(int id);


    /**
     * 一个月内热门文章
     *
     * @param end
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM social_blog.paper p WHERE 1=1",
            "<if test=\"endTime!=null\">",
            "AND p.create_time > #{endTime}",
            "</if>",
            " ORDER BY p.mark DESC",
            "</script>"})
    List<Paper> findPaperInMonth(@Param("endTime") Timestamp end);

    /**
     * 返回文章和评论信息 按热度排序
     *
     * @return
     */
    @Select("SELECT * FROM paper WHERE is_pass=\"审核通过\" ORDER BY mark DESC LIMIT 0,3")
    List<Paper> findAllPapersByMark();

    /**
     * 通过ID查找文章标题
     *
     * @param comments_paper
     * @return
     */
    @Select("SELECT p.title FROM social_blog.paper p WHERE p.id = #{id}")
    String findPaperTitleById(int comments_paper);

    /**
     * 首页按文章发布时间最新排序(没有限制)
     *
     * @return
     */
    @Select("SELECT * FROM social_blog.paper WHERE is_pass = \"审核通过\" ORDER BY create_time DESC")
    List<Paper> findAllPapersByTimeNoLimit();

    /**
     * 个人页面所有文章,通过用户姓名查找该用户的所有文章
     *
     * @param search
     * @param name
     * @return
     */
    @Select({
            "<script>",
            "SELECT p.* FROM social_blog.paper p , social_blog.users u WHERE p.author = u.id AND u.login_name = #{name}",
            "<if test=\"search!=null and search!=''\">",
            "AND  p.title LIKE concat(concat('%',#{search}),'%') ",
            "</if>",
            "ORDER BY p.create_time DESC",
            "</script>"
    })
    List<Paper> findPaperByUserName(@Param("name") String name, @Param("search") String search);

    /**
     * 文章库
     *
     * @param search
     * @param search
     * @return
     */
    @Select({"<script>",
            "SELECT p.* FROM social_blog.paper p , social_blog.users s WHERE s.id = p .author AND  p.is_pass = \"审核通过\"",
            "<if test=\"search!=null and search!=''\">",
            "AND  p.title LIKE concat(concat('%',#{search}),'%') ",
            "</if>",
            "ORDER BY  p.create_time DESC",
            "</script>" })
    List<Paper> findAllPapers(@Param("search") String search);

    /**
     * 通过用户统计该用户文章数量
     */
    @Select("SELECT COUNT(*) FROM social_blog.paper p LEFT JOIN social_blog.users u ON u.id = p.author WHERE u.login_name=#{name}")
    int findCountOfPaperByUser(String name);

    /**
     * 通过ID查找标签
     *
     * @param id
     * @return
     */
    @Select("SELECT t.tag_name " +
            "FROM social_blog.paper p , social_blog.tag t " +
            "WHERE t.paper_id = p.id AND p.id = #{id} ")
    List<String> findTagsById(int id);

    /**
     * 查到所有的文章信息
     *
     * @param name  user's name
     * @param page  = pageNum*limit-1
     * @param limit
     */
    @Select("SELECT p.* FROM social_blog.paper p LEFT JOIN social_blog.users u ON u.id = p.author WHERE u.login_name=#{name} LIMIT #{page},#{limit}")
    List<Paper> findPaperByUser(@Param("name") String name, @Param("page") int page, @Param("limit") int limit);

    /**
     * 更新文章内容
     *
     * @param content
     * @param id
     * @return
     */
    @Update("UPDATE social_blog.paper p SET p.content = #{content} WHERE p.id = #{id} ")
    int updatePaperContentById(@Param("content") String content, @Param("id") int id);

    @Delete("delete from social_blog.column_paper  where paper_id=#{id}")
    int deletePaperInCoulmn(int id);

    /**
     * 根据id删除paper信息
     *
     * @param id paperid
     * @return
     */
    @Delete("DELETE FROM social_blog.paper WHERE id=#{id}")
    int deletePaperByPaperId(int id);

    /**
     * 查询所有文章用于管理员的审查 物理分页
     *
     * @param page      页数
     * @param limit     每个项数
     * @param search    查询内容
     * @param startTime 现在时间
     * @param endTime   前n天 比如现在时间 。。。 nowday的几天前就是endtime
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM social_blog.paper p WHERE 1=1",
            "<if test=\"search!=null and search!=''\">",
            "AND p.title LIKE concat(concat('%',#{search}),'%')",
            "</if>",
            "<if test=\"endTime!=null\">",
            "AND p.create_time > #{endTime}",
            "</if>",
            "LIMIT #{page},#{limit}",
            "</script>"})
    List<Paper> findPapers(@Param("page") int page,
                           @Param("limit") int limit,
                           @Param("search") String search,
                           @Param("startTime") Timestamp startTime,
                           @Param("endTime") Timestamp endTime);

    /**
     * 更改文章状态执行方法
     *
     * @param is_pass 输入状态 string类型
     * @param id      文章id
     * @return
     */
    @Update("UPDATE social_blog.paper SET is_pass = #{is_pass} WHERE id = #{id}")
    int updataPaperStatus(@Param("id") int id, @Param("is_pass") String is_pass);

    @Select("SELECT COUNT(*) FROM social_blog.paper")
    int findAllPaperCount();


    /**
     * @param title      标题
     * @param content    文章正文
     * @param createTime 创建时间
     * @param author     作者
     * @param segment    片段
     * @param mark       初始设为一
     * @param is_pass    初始设置 正在审核
     * @return
     */
    @Insert("INSERT INTO social_blog.paper(title,content,create_time,mark,author,segment,paper_image,is_pass) VALUES (#{title},#{content},#{createTime},#{mark},#{author},#{segment},#{paper_image},#{is_pass})")
    int insertPaper(@Param("title") String title, @Param("content") String content, @Param("createTime") Timestamp createTime, @Param("author") int author, @Param("segment") String segment, @Param("paper_image") String paper_image,@Param("mark") int mark, @Param("is_pass") String is_pass);

    /**
     * 根据信息查id
     * @param title
     * @param segment
     * @param author
     * @return
     */
    @Select("SELECT id FROM social_blog.paper WHERE title=#{title} AND segment=#{segment} AND author = #{author}")
    int findPaperId(@Param("title") String title,@Param("segment") String segment,@Param("author") int author);

}



